package au.com.mason.propertyTracker.controller;

import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import au.com.mason.propertyTracker.dto.Report;
import au.com.mason.propertyTracker.service.ReportService;

@Controller
public class ShowReport {
	
	private ReportService reportService;
	
	@Autowired
	public ShowReport(ReportService reportService) {
		this.reportService = reportService;
	}
	
	@RequestMapping("/ShowReport.action")
	public String displayReport(HttpServletResponse response) {
		
		List<Report> reports = reportService.getResults();
		
		OutputStream out = null;
		try {
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=sampleName.xls");
			WritableWorkbook w = Workbook.createWorkbook(response.getOutputStream());
			WritableSheet s = w.createSheet("Demo", 0);
			

			
			s.addCell(getBoldLabel(0, 0, "Suburb"));
			s.addCell(getBoldLabel(1, 0, "Month"));
			s.addCell(getBoldLabel(2, 0, "Year"));
			s.addCell(getBoldLabel(3, 0, "Bedrooms"));
			s.addCell(getBoldLabel(4, 0, "Listed"));
			s.addCell(getBoldLabel(5, 0, "With Price"));
			s.addCell(getBoldLabel(6, 0, "Sold/Removed"));
			s.addCell(getBoldLabel(7, 0, "Avg Price"));
			s.addCell(getBoldLabel(8, 0, "Avg Time"));
			s.addCell(getBoldLabel(9, 0, "Rentals"));
			s.addCell(getBoldLabel(10, 0, "Removed"));
			s.addCell(getBoldLabel(11, 0, "Avg Rent"));
			s.addCell(getBoldLabel(12, 0, "Avg Time"));
			
			int row = 1;
			int column = 0;
			WritableCellFormat numberFormat = new WritableCellFormat(NumberFormats.THOUSANDS_INTEGER);
			WritableCellFormat decimalFormat = new WritableCellFormat(NumberFormats.INTEGER);
			NumberFormat dp3 = new NumberFormat("#.##");
			for (Report report : reports) {
				s.addCell(new Label(column++, row, report.getSuburb().getName()));
				s.addCell(new Label(column++, row, report.getMonth()));
				s.addCell(new Number(column++, row, report.getYear().intValue()));
				s.addCell(new Number(column++, row, (report.getNumberBedrooms() != null)?report.getNumberBedrooms().doubleValue():0));
				s.addCell(new Number(column++, row, (report.getPropertiesListed() != null)?report.getPropertiesListed().doubleValue():0));
				s.addCell(new Number(column++, row, (report.getPropertiesListedWithPrice() != null)?report.getPropertiesListedWithPrice().doubleValue():0));
				s.addCell(new Number(column++, row, (report.getPropertiesSold() != null)?report.getPropertiesSold().doubleValue():0));
				Number averagePrice = new Number(column++, row, (report.getAveragePrice() != null)?report.getAveragePrice().doubleValue():0);
				averagePrice.setCellFormat(numberFormat);
				s.addCell(averagePrice);
				Number propertyTOM = new Number(column++, row, (report.getAveragePropertyOnMarket() != null)?report.getAveragePropertyOnMarket().doubleValue():0);
				propertyTOM.setCellFormat(decimalFormat);
				s.addCell(propertyTOM);
				s.addCell(new Number(column++, row, (report.getRentalsListed() != null)?report.getRentalsListed().doubleValue():0));
				s.addCell(new Number(column++, row, (report.getRenatalsRemoved() != null)?report.getRenatalsRemoved().doubleValue():0));
				Number averageRent = new Number(column++, row, (report.getAverageRent() != null)?report.getAverageRent().doubleValue():0);
				averageRent.setCellFormat(decimalFormat);
				s.addCell(averageRent);
				Number rentalTOM = new Number(column++, row, (report.getAverageRentalOnMarket() != null)?report.getAverageRentalOnMarket().doubleValue():0);
				rentalTOM.setCellFormat(decimalFormat);
				s.addCell(rentalTOM);
				row++;
				column = 0;
			}
			
			w.write();
			w.close();
		} 
		catch (Exception e){
			e.printStackTrace();
		} 
		finally {
			if (out != null) {
				try {
					out.close();
				}
				catch (Exception e) {
					e.printStackTrace();
				}
		  	}
		}

		  return "showReport";
	}

	private Label getBoldLabel(int column, int row, String label) {
		WritableFont boldFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD); 	
		WritableCellFormat bold = new WritableCellFormat(boldFont);
		Label labelCell = new Label(column, row, label);
		labelCell.setCellFormat(bold);
		
		return labelCell;
	}

}
